/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.BugRS.dao;

import com.aptech.BugRS.enity.ComplaintDTO;
import com.aptech.BugRS.util.Configure;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author KISS
 */
/*
 * Class D_Complaint using insert,update,view for Complaint
 */
public class ComplaintDAO {
//Only add, update
//Not delete

    private Configure db = null;
    private Connection con = null;
    private ResultSet rs = null;
    private PreparedStatement pst = null;
    private String lastError = "";

    /**
     * Creates a new instance of D_Complaint
     */
    public ComplaintDAO() {
        db = new Configure();
    }

    /**
     * 
     * @return list of Complaint
     */
    public ArrayList<ComplaintDTO> ReadByAll() {

        ArrayList<ComplaintDTO> list = new ArrayList<ComplaintDTO>();
        con = db.getConnection();
        String sql = "SELECT * FROM Complaint";
        try {
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                //Complaint com = new Complaint();
                ComplaintDTO com = new ComplaintDTO();
                com.setComplaintID(rs.getInt(1));
                com.setDispatchNo(rs.getInt(2));
                com.setDepartmentID(rs.getInt(3));
                com.setCustomerID(rs.getInt(4));
                com.setComTitle(rs.getString(5));
                com.setComContent(rs.getString(6));
                com.setComDate(rs.getDate(7));
                com.setComStatus(rs.getString(8));
                list.add(com);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error");
        } finally {
            db.closeConnection();
        }
        return list;
    }

    /**
     * ReadByID of Complaint
     * @param id
     * @return
     */
    public ComplaintDTO ReadByID(int id) {
        ComplaintDTO com = new ComplaintDTO();
        try {
            con = db.getConnection();
            String sql = "SELECT * FROM Complaint WHERE ComplaintID = ?";
            pst = con.prepareStatement(sql);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            com.setComplaintID(rs.getInt(1));
            com.setDispatchNo(rs.getInt(2));
            com.setDepartmentID(rs.getInt(3));
            com.setCustomerID(rs.getInt(4));
            com.setComTitle(rs.getString(5));
            com.setComContent(rs.getString(6));
            com.setComDate(rs.getDate(7));
            com.setComStatus(rs.getString(8));
        } catch (SQLException ex) {
            setLastError("SQL Error");
        }
        return com;
    }

    /**
     * 
     * @return list Complaint not replied
     */
    public ArrayList<ComplaintDTO> ReadByStatus0() {
        ArrayList<ComplaintDTO> list = new ArrayList<ComplaintDTO>();
        String sql = "SELECT * FROM Complaint Where Comstatus = 0";
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                ComplaintDTO com = new ComplaintDTO();
                com.setComplaintID(rs.getInt(1));
                com.setDispatchNo(rs.getInt(2));
                com.setDepartmentID(rs.getInt(3));
                com.setCustomerID(rs.getInt(4));
                com.setComTitle(rs.getString(5));
                com.setComContent(rs.getString(6));
                com.setComDate(rs.getDate(7));
                com.setComStatus(rs.getString(8));
                list.add(com);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error");
        } finally {
            db.closeConnection();
        }
        return list;
    }

    /**
     *
     * @return list complied is replied
     */
    public ArrayList<ComplaintDTO> ReadByStatus1() {
        ArrayList<ComplaintDTO> list = new ArrayList<ComplaintDTO>();
        String sql = "SELECT * FROM Complaint Where Comstatus = 1";
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                ComplaintDTO com = new ComplaintDTO();
                com.setComplaintID(rs.getInt(1));
                com.setDispatchNo(rs.getInt(2));
                com.setDepartmentID(rs.getInt(3));
                com.setCustomerID(rs.getInt(4));
                com.setComTitle(rs.getString(5));
                com.setComContent(rs.getString(6));
                com.setComDate(rs.getDate(7));
                com.setComStatus(rs.getString(8));
                list.add(com);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error");
        } finally {
            db.closeConnection();
        }
        return list;
    }

    /**
     * Method add new complaint
     * @param com
     * @return boolean
     */
    public boolean Create(ComplaintDTO com) {
        ArrayList<ComplaintDTO> list = ReadByAll();

        /*
         * Check exits in database
         */
        for (ComplaintDTO co : list) {
            if (co.getComplaintID() == com.getComplaintID()) {
                setLastError("Complaint is exitsed! Add fail!");
                return false;
            }
        }

        /*
         * Check validate
         */
//        if (!com.validate()) {
//            setLastError(com.getLastError());
//            return false;
//        }
        try {
            con = db.getConnection();
            String sql = "INSERT INTO Complaint VALUES(?,?,?,?,?,?,?)";
            pst = con.prepareStatement(sql);
            pst.setInt(1, com.getComplaintID());
            pst.setInt(2, com.getDispatchNo());
            pst.setInt(3, com.getDepartmentID());
            pst.setInt(4, com.getCustomerID());
            pst.setString(5, com.getComTitle());
            pst.setString(6, com.getComContent());
            pst.setDate(7, com.getComDate());
            pst.setString(8, com.getComStatus());
            if (pst.executeUpdate() > 0) {
                setLastError("Add Complaint Successfully!");
                db.closeConnection();
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
            db.closeConnection();
            return false;
        }
        setLastError("Add fail!");
        db.closeConnection();
        return false;
    }

    /**
     *
     * @param complaint
     * @return false if not exit, and return true if exits
     */
    public boolean isExits(ComplaintDTO com) {
        try {
            String sql = "SELECT * FROM Complaint WHERE ComplaintID = ?";
            con = db.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, com.getComplaintID());
            rs = pst.executeQuery();
            /**
             * Exits return true
             * Not Exit return false;
             */
            if (rs.next()) {
                return true;
            }

        } catch (SQLException ex) {
            setLastError("SQL Error");
        }
        return false;
    }

    /**
     * Method Update a Complaint
     * @param complaint
     * @return boolean
     */
    public boolean Update(ComplaintDTO com) {
        try {
            con = db.getConnection();
            String sql = "SELECT * FROM Complaint WHERE ComplaintID = ?";
            pst = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, com.getComplaintID());
            rs = pst.executeQuery();
            rs.first();
            rs.updateInt(2, com.getDispatchNo());
            rs.updateInt(3, com.getDepartmentID());
            rs.updateInt(4, com.getCustomerID());
            rs.updateString(5, com.getComTitle());
            rs.updateString(6, com.getComContent());
            rs.updateDate(7, com.getComDate());
            rs.updateString(8, com.getComStatus());
            rs.updateRow();
            db.closeConnection();
            setLastError("Update Comlaint successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError("SQL Error");
            db.closeConnection();
            return false;
        }
    }

    /**
     *Update Complaint By status
     * @param id
     * @param status
     * @return boolean
     */
    public boolean UpdateByStatus(int id, int status) {
        try {
            con = db.getConnection();
            String sql = "SELECT * FROM Complaint WHERE ComplaintID = ?";
            pst = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            rs.first();
            rs.updateInt(8, status);
            rs.updateRow();
            db.closeConnection();
            setLastError("Update Comlaint successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError("SQL Error");
            db.closeConnection();
        }
        return false;
    }

    /**
     * Count total unreplied complaint
     * @return Total Complaint un-replied
     */
    public int CountByStatus0() {
        int temp = 0;
        try {
            con = db.getConnection();
            String sql = "SELECT COUNT(*) FROM Complaint WHERE ComStatus = '0'";
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();

            while (rs.next()) {
                temp = rs.getInt(1);
            }
            db.closeConnection();
            return temp;

        } catch (SQLException ex) {
            setLastError("SQL Error");
            db.closeConnection();
        }
        return 0;
    }

    /**
     * Count total replied Complaint
     * @return total Complaint Replied
     */
    public int CountByStatus1() {
        int temp = 0;
        try {
            con = db.getConnection();
            String sql = "SELECT COUNT(*) FROM Complaint WHERE ComStatus = '0'";
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();

            while (rs.next()) {
                temp = rs.getInt(1);
            }
            db.closeConnection();
            return temp;

        } catch (SQLException ex) {
            setLastError("SQL Error");
            db.closeConnection();
        }
        return 0;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}
